package com.hsnn.medstgmini.settlement.dao.impl;

import java.util.HashMap;
import java.util.Map;




import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.base.std.model.StdHealthbureau;
import com.hsnn.medstgmini.base.std.model.form.StdHealthbureauForm;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.settlement.dao.SetPaynoticeDao;
import com.hsnn.medstgmini.settlement.model.SetAcctInfo;
import com.hsnn.medstgmini.settlement.model.SetRelationship;
import com.hsnn.medstgmini.settlement.model.form.SetPaynoticeForm;
import com.hsnn.medstgmini.settlement.service.SetPaynoticeManager;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.PinYin;


@Repository
public class SetPaynoticeDaoImpl extends BaseHibernateDAO implements  SetPaynoticeDao {
	
	@Autowired
	private SetPaynoticeManager setPaynoticeManager;
	
	@Autowired
	private HibernateSqlUtil dao;

	
	/**
	 * 缴款通知书[中心端]
	 */
	@Override
	public Pagination getSettleInfoList(Pagination page) {
		StringBuffer sql = new StringBuffer()
		.append(" select a.notice_id as noticeId, a.notice_name as noticeName, a.notice_no as noticeNo,a.schedule_beg_time as scheduleBegTime,  a.schedule_end_time as scheduleEndTime,  ")
		.append(" a.recp_org_id as hospId, a.rep_order_count as repoOrderCount, a.rep_hosp_count as repHospCount,a.amount,  ")
		.append(" b.hosp_id as recpOrgId,b.hosp_name as hospName,b.hosp_code as hospCode ")
		.append(" from set_paynotice a ")
		.append(" left join std_hospinfo b on b.hosp_Id=a.recp_org_id")
		.append(" where notice_type=1");
		/*-.append(" union all")
		.append(" select a.notice_id as noticeId, a.notice_name as noticeName, a.notice_no as noticeNo,a.schedule_beg_time as scheduleBegTime,  a.schedule_end_time as scheduleEndTime, ")
		.append(" a.recp_org_id as hospName, a.rep_order_count as repoOrderCount, a.rep_hosp_count as repHospCount,a.amount,")
		.append(" c.id as noticeId,c.`name` as noticeName")
		.append(" from set_paynotice a ")
		.append(" left join sys_user c on c.id= a.recp_org_id")
		.append(" where notice_type=2");*/
		 Map<String, Object> params = new HashMap<String, Object>();
		 SetPaynoticeForm form = (SetPaynoticeForm) page.getForm();
		 if (StringUtils.isNotBlank(form.getNoticeId())){
			 sql.append(" and a.notice_id=:noticeId");
			 params.put("noticeId", form.getNoticeId());
		 }
		 if (StringUtils.isNotBlank(form.getNoticeNo())){
			 sql.append(" and a.notice_no=:noticeNo");
			 params.put("noticeNo", form.getNoticeNo());
		 }
		 if (StringUtils.isNotBlank(form.getNoticeName())){
			 sql.append(" and a.notice_name=:noticeName");
			 params.put("noticeName", form.getNoticeName());
		 }
		 if (StringUtils.isNotBlank(form.getRecpOrgId())){
			 sql.append(" and a.recp_org_id=:recpOrgId");
			 params.put("recpOrgId", form.getRecpOrgId());
		 }
		 if (StringUtils.isNotBlank(form.getScheduleBegTime())){
			 sql.append(" and a.schedule_beg_time=:scheduleBegTime");
			 params.put("scheduleBegTime", form.getScheduleBegTime());
		 }
		 if (StringUtils.isNotBlank(form.getScheduleEndTime())){
			 sql.append(" and a.schedule_end_time=:scheduleEndTime");
			 params.put("scheduleEndTime", form.getScheduleEndTime());
		 }
		 return dao.getSqlPageMapList(sql.toString(), params, page);
	}


	/**
	 * 缴款通知书[医疗机构端]
	 */
	@Override
	public Pagination getSettleInfoListyy(Pagination page) {
			StringBuffer sql = new StringBuffer()
			.append(" select a.notice_id as noticeId, a.notice_name as noticeName, a.notice_no as noticeNo,a.schedule_beg_time as scheduleBegTime,  a.schedule_end_time as scheduleEndTime, ")
			.append(" a.recp_org_id as hospId, a.rep_order_count as repOrderCount, a.rep_hosp_count as repHospCount,a.amount, ")
			.append("  b.hosp_id as recpOrgId,b.hosp_code as hospCode,b.hosp_name as hospName ")
			.append(" from set_paynotice a ")
			.append(" left join std_hospinfo b on b.hosp_id=a.recp_org_id")
			.append(" where a.notice_type=1 and a.recp_org_id = :hospId ");
			 Map<String, Object> params = new HashMap<String, Object>();
			 SetPaynoticeForm form = (SetPaynoticeForm) page.getForm();
				 params.put("hospId", form.getHospId());
			 if (StringUtils.isNotBlank(form.getNoticeId())){
				 sql.append(" and a.notice_id=:noticeId");
				 params.put("noticeId", form.getNoticeId());
			 }
			 if (StringUtils.isNotBlank(form.getNoticeNo())){
				 sql.append(" and a.notice_no=:noticeNo");
				 params.put("noticeNo", form.getNoticeNo());
			 }
			 if (StringUtils.isNotBlank(form.getNoticeName())){
				 sql.append(" and a.notice_name like :noticeName");
				 params.put("noticeName", "%"+form.getNoticeName()+"%");
			 }
			 if (StringUtils.isNotBlank(form.getRecpOrgId())){
				 sql.append(" and a.recp_org_id=:recpOrgId");
				 params.put("recpOrgId", form.getRecpOrgId());
			 }
			 if (StringUtils.isNotBlank(form.getScheduleBegTime())){
				 sql.append(" and a.schedule_beg_time=:scheduleBegTime");
				 params.put("scheduleBegTime", form.getScheduleBegTime());
			 }
			 if (StringUtils.isNotBlank(form.getScheduleEndTime())){
				 sql.append(" and a.schedule_end_time=:scheduleEndTime");
				 params.put("scheduleEndTime", form.getScheduleEndTime());
			 }
			 return dao.getSqlPageMapList(sql.toString(), params, page);
		}

	
	/**
	 * 缴款通知书[核算中心]
	 */
	@Override
	public Pagination getSettleInfoLisths(Pagination page) {
			StringBuffer sql = new StringBuffer()
			.append(" select a.notice_id as noticeId, a.notice_name as noticeName, a.notice_no as noticeNo,a.schedule_beg_time as scheduleBegTime,  a.schedule_end_time as scheduleEndTime, ")
			.append(" a.recp_org_id as heaBurId, a.rep_order_count as repoOrderCount, a.amount,  ")
			.append(" b.hea_bur_id as recpOrgId, b.hea_bur_code as heaBurCode, b.hea_bur_name as heaBurName")
			.append(" from set_paynotice a ")
			.append(" left join std_healthbureau b on b.hea_bur_id=a.recp_org_id")
			.append(" where a.notice_type=2");
			 Map<String, Object> params = new HashMap<String, Object>();
			 StdHealthbureauForm form = (StdHealthbureauForm) page.getForm();
		
			 if (StringUtils.isNotBlank(form.getNoticeId())){
				 sql.append(" and a.notice_id=:noticeId");
				 params.put("noticeId", form.getNoticeId());
			 }
			 if (StringUtils.isNotBlank(form.getNoticeNo())){
				 sql.append(" and a.notice_no=:noticeNo");
				 params.put("noticeNo", form.getNoticeNo());
			 }
			 if (StringUtils.isNotBlank(form.getNoticeName())){
				 sql.append(" and a.notice_name=:noticeName");
				 params.put("noticeName", form.getNoticeName());
			 }
			 if (StringUtils.isNotBlank(form.getRecpOrgId())){
				 sql.append(" and a.recp_org_id=:recpOrgId");
				 params.put("recpOrgId", form.getRecpOrgId());
			 }
			 if (StringUtils.isNotBlank(form.getScheduleBegTime())){
				 sql.append(" and a.schedule_beg_time=:scheduleBegTime");
				 params.put("scheduleBegTime", form.getScheduleBegTime());
			 }
			 if (StringUtils.isNotBlank(form.getScheduleEndTime())){
				 sql.append(" and a.schedule_end_time=:scheduleEndTime");
				 params.put("scheduleEndTime", form.getScheduleEndTime());
			 }
			 if (StringUtils.isNotBlank(form.getHeaBurName())){
				 sql.append(" and a.hea_bur_name=:heaBurName");
				 params.put("heaBurName", form.getHeaBurName());
			 }
			 return dao.getSqlPageMapList(sql.toString(), params, page);
		}
	////////////////////////////////////////////////////////////////////////////
	/**
	 * 医疗机构交货通知书详情[医疗机构端]
	 */
	@Override
	public Pagination getSetPaynoticeDetailList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		/*.append(" select distinct ssdi.bank_acct_trans_type as bankAcctTransType, ")
		.append(" ssdi.pro_price as  proPrice, ")
		.append(" ssdi.recp_count as  recpCount, ")
		.append(" vd.drug_code AS drugCode, ")
		.append(" vd.drug_name AS drugName, ")
		.append(" vd.form_name AS formName, ")
		.append(" vd.spec_name AS specName, ")
		.append(" vd.factor,                ")
		.append(" ssdi.drug_id as drugId,             ")
		.append(" ssdi.comp_id as compId,             ")
		.append("  vd.prod_comp_id as prodCompId,        ")
		.append(" vd.unit,                  ")
		.append(" vd.material_name AS materialName, ")
		.append(" vd.comp_name AS prodComp, ")
		.append(" sc.comp_name AS compName ")
		.append(" from set_paynotice sp , ")
		.append(" set_paynotice_detail spd , ")
		.append(" set_settletdetailinfo ssdi, ")
		.append(" view_drug vd, ")
		.append(" std_compinfo sc ")
		.append(" where sp.notice_id = spd.payment_notice_id ")
		.append(" and spd.bank_acct_trans_info_id = ssdi.trans_id ")
		.append(" and ssdi.drug_id = vd.drug_id ")
		.append(" and sp.notice_type = 1 ")
		.append(" and sc.comp_id = ssdi.comp_id ")
		.append(" and ssdi.bank_acct_trans_type = 1 ")
		//.append(" and ssdi.is_in_settle_slip = 0 ")
	    .append(" and sp.notice_id=:noticeId ");*/
		sql.append(" select ssdl.bank_acct_trans_type as bankAcctTransType,ssdl.pro_price as  proPrice, ssdl.recp_count as  recpCount, ");
		sql.append(" c.drug_code AS drugCode, c.qualityspec_name AS drugName, c.form_name AS formName, c.spec_name AS specName,  ");
		sql.append(" c.factor, ssdl.drug_id as drugId,	ssdl.comp_id as compId, c.prod_compcode as prodCompId,c.unit_name as unit,   ");
		sql.append(" c.material_name AS materialName, c.prod_compname AS prodComp, sc.comp_name AS compName   ");
		sql.append(" from set_paynotice_detail spd ");
		sql.append(" left join  set_paynotice a   on a.notice_id = spd.payment_notice_id  ");
		sql.append(" left join set_settletdetailinfo ssdl on   spd.bank_acct_trans_info_id = ssdl.trans_id ");
		sql.append(" left join std_hospinfo b on b.hosp_id=ssdl.hosp_id ");
		sql.append(" left join base_goods_item c on c.goods_code = ssdl.goods_code ");
	//	sql.append("  left join view_drug vdg on ssdl.drug_id = vdg.drug_id ");
		sql.append(" left join std_compinfo sc on sc.comp_id = ssdl.comp_id ");
		sql.append(" where a.notice_type=1 and b.hosp_id = :hospId and a.notice_id = :noticeId ");

		String bankAcctTransType = page.getConditions().get("bankAcctTransType");
		String drugCode = page.getConditions().get("drugCode");
		String drugName = page.getConditions().get("drugName");
		if(StringUtils.isNotBlank(bankAcctTransType)){
			sql.append(" and ssdi.bank_acct_trans_type = :bankAcctTransType ");
		}
		if(StringUtils.isNotBlank(drugCode)){
			sql.append(" and c.drug_code = :drugCode ");
		}
		/*if(StringUtils.isNotBlank(drugName)){
			sql.append(" and vd.drug_name like :drugName");
			page.getConditions().put("drugName", "%"+drugName+"%");
		}*/
		if (StringUtils.isNotBlank(drugName)) {// 通用名
			if (PinYin.isPinYin(drugName)) {
				sql.append(" and c.drug_name_spel like :drugName");
				page.getConditions().put("drugName", "%"+ drugName + "%");
			} else {
				sql.append(" and c.drug_name like :drugName");
				page.getConditions().put("drugName", "%" + drugName+ "%");
			}
		}
		sql.append(" order by drugName ");
		return dao.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}

	/**
	 * 缴款通知书详情[核算中心]
	 */
	@Override
	public Pagination getSetPaynoticehsDetailList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		/*-.append(" select a.notice_id as noticeId, a.notice_name as noticeName, a.notice_no as noticeNo,a.schedule_beg_time as scheduleBegTime,  a.schedule_end_time as scheduleEndTime,   ")
		.append(" a.recp_org_id as recpOrgId, a.rep_order_count as repoOrderCount, a.rep_hosp_count as repHospCount,a.amount,")
		.append(" b.org_id as orgId, b.father_org_id as fatherOrgId, b.father_org_name as fatherOrgName , ")
		.append(" c.hea_bur_id as heaBurId, c.hea_bur_code as heaBurCode, c.hea_bur_name as heaBurName")
		.append(" from set_paynotice a  ")
		.append(" LEFT JOIN set_relationship b on a.recp_org_id=b.org_id")
		.append(" left join std_healthbureau c on c.hea_bur_id = a.recp_org_id")
		.append(" where a.notice_type=1 ")
		.append(" and b.father_org_id=:heaBurId");*/
		
		sql.append("SELECT d.trans_id AS transId, d.del_detail_id AS delDetailId, d.goods_code AS goodsCode, d.drug_id AS drugId, d.pro_count AS proCount, d.recp_count AS recpCount, d.settle_sch_beg_time AS settleSchBegTime, d.settle_sch_end_time AS settleSchEndTime, d.is_in_settle_slip AS isInSettleSlip, d.add_settle_slip_dttm AS addSettleSlipDttm, d.pro_price AS proPrice, d.pro_price * d.recp_count AS amount, d.recp_datetime recpDateTime, d.pro_datetime proDateTime, e.drug_code drugCode, e.drug_name drugName, e.comp_name prodCompName, e.prod_comp_id prodCompId, e.form_name formName, e.spec_name specName, e.factor, e.unit, e.material_name materialName, f.comp_id compId, f.comp_name compName, g.hosp_id hospId, g.hosp_name hospName FROM set_paynotice a JOIN set_paynotice_detail b ON a.notice_id = b.bank_acct_trans_info_id JOIN set_paynotice_detail c ON a.notice_id = c.payment_notice_id JOIN set_settletdetailinfo d ON c.bank_acct_trans_info_id = d.trans_id JOIN view_drug e ON d.drug_id = e.drug_id JOIN std_compinfo f ON d.comp_id = f.comp_id JOIN std_hospinfo g ON d.hosp_id = g.hosp_id WHERE b.payment_notice_id = :noticeId ");
		sql.append(" SELECT                                          ");
		sql.append(" d.trans_id AS transId,                          ");
		sql.append(" d.del_detail_id AS delDetailId,                 ");
		sql.append(" d.goods_code AS goodsCode,                      ");
		sql.append(" d.drug_id AS drugId,                            ");
		sql.append(" d.pro_count AS proCount,                        ");
		sql.append(" d.recp_count AS recpCount,                      ");
		sql.append(" d.settle_sch_beg_time AS settleSchBegTime,      ");
		sql.append(" d.settle_sch_end_time AS settleSchEndTime,      ");
		sql.append(" d.is_in_settle_slip AS isInSettleSlip,          ");
		sql.append(" d.add_settle_slip_dttm AS addSettleSlipDttm,    ");
		sql.append(" d.pro_price AS proPrice,                        ");
		sql.append(" d.pro_price * d.recp_count AS amount,           ");
		sql.append(" d.recp_datetime recpDateTime,                   ");
		sql.append(" d.pro_datetime proDateTime,                     ");
		sql.append(" e.drug_code drugCode,                           ");
		sql.append(" e.qualityspec_name drugName,                    ");
		sql.append("  e.prod_compname prodCompName,                  ");
		sql.append(" e.form_name formName,                           ");
		sql.append(" e.spec_name specName,                           ");
		sql.append(" e.factor,                                       ");
		sql.append(" e.unit_name as unit,                            ");
		sql.append(" e.material_name materialName,                   ");
		sql.append(" f.comp_id compId,                               ");
		sql.append(" f.comp_name compName,                           ");
		sql.append(" g.hosp_id hospId,                               ");
		sql.append(" g.hosp_name hospName                            ");
		sql.append(" FROM set_paynotice a                            ");
		sql.append(" JOIN set_paynotice_detail b ON a.notice_id = b.bank_acct_trans_info_id  ");
		sql.append(" JOIN set_paynotice_detail c ON a.notice_id = c.payment_notice_id        ");
		sql.append(" JOIN set_settletdetailinfo d ON c.bank_acct_trans_info_id = d.trans_id  ");
		sql.append(" left join base_goods_item e on e.goods_code = d.goods_code              ");
		sql.append(" JOIN std_compinfo f ON d.comp_id = f.comp_id                            ");
		sql.append(" JOIN std_hospinfo g ON d.hosp_id = g.hosp_id                            ");
		sql.append(" WHERE b.payment_notice_id = :noticeId                                   ");
		Map<String, Object> params = new HashMap<String, Object>();
		StdHealthbureauForm form = (StdHealthbureauForm) page.getForm();
		
		params.put("noticeId", form.getNoticeId());
		
		if (StringUtils.isNotBlank(form.getDrugCode())){
			 sql.append(" and e.drug_code = :drugCode");
			 params.put("drugCode", form.getDrugCode());
		 }
		if (StringUtils.isNotBlank(form.getDrugName())){
			 sql.append(" and e.drug_name LIKE :drugName");
			 params.put("drugName", "%" + form.getDrugName() + "%");
		 }
		
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	/**
	 * 缴款通知书详情[中心端]
	 */
	@Override
	public Pagination getSetPaynoticeDetailListzx(Pagination page) {
		StringBuffer sql = new StringBuffer()
		.append(" select a. trans_id as transId, ")
		.append(" a.goods_code as goodsCode,a.pro_count as proCount,a.recp_count as recpCount, a.pro_price * a.recp_count AS amount, ")
		.append(" a.pro_price as proPrice ")
		.append(" from set_settletdetailinfo a ")
		.append(" join set_paynotice_detail b on b.bank_acct_trans_info_id= a.trans_id ")
		.append(" join set_paynotice c on c.notice_id=b.payment_notice_id ")
		.append(" where c.notice_type=1  AND c.notice_id = :noticeId ");
		
/*		.append(" select a. trans_id as transId, a.order_id as orderId,  a.del_detail_id as delDetailId, ")
		.append(" a.goods_code as goodsCode, a.drug_id as drugId,a.pro_count as proCount,a.recp_count as recpCount, a.pro_price * a.recp_count AS amount, ")
		.append(" a.settle_sch_beg_time as settleSchBegTime,a.settle_sch_end_time as settleSchEndTime, ")
		.append(" a.is_in_settle_slip as isInSettleSlip,a.add_settle_slip_dttm as addSettleSlipDttm,a.pro_price as proPrice, ")
		.append(" c.notice_id as noticeId,c.notice_no as noticeNo,c.notice_name as noticeName ")
		.append(" from set_settletdetailinfo a ")
		.append(" join set_paynotice_detail b on b.bank_acct_trans_info_id= a.trans_id ")
		.append(" join set_paynotice c on c.notice_id=b.payment_notice_id ")
		.append(" where c.notice_type=1  AND c.notice_id = :noticeId ");*/
		
		Map<String, Object> params = new HashMap<String, Object>();
		SetPaynoticeForm form = (SetPaynoticeForm) page.getForm();
		params.put("noticeId", form.getNoticeId());
		if (StringUtils.isNotBlank(form.getGoodsCode())){
			 sql.append(" and a.goods_code=:goodsCode");
			 params.put("goodsCode", form.getGoodsCode());
		 }
		page.setRows(dao.getSqlMapList(sql.toString(), params)); 
		return page;
	}
	
	/**
	  * @category 获取缴款通知书列表[卫生局]
	  * @Auther 周奇
	  * @date 2015年8月6日15:52:11
	  * @param page
	  * @return Pagination
	  */
	@Override
	public Pagination getPaynoticListForHeathBeau(Pagination page) {
			StringBuffer sql = new StringBuffer()
			.append(" select a.notice_id as noticeId, a.notice_name as noticeName, a.notice_no as noticeNo,a.schedule_beg_time as scheduleBegTime,  a.schedule_end_time as scheduleEndTime, ")
			.append(" a.recp_org_id as heaBurId, a.rep_order_count as repoOrderCount, a.rep_hosp_count repHospCount, a.amount,  ")
			.append(" b.hea_bur_id as recpOrgId, b.hea_bur_code as heaBurCode, b.hea_bur_name as heaBurName ")
			.append(" from set_paynotice a ")
			.append(" left join std_healthbureau b on b.hea_bur_id = a.recp_org_id ")
			.append(" where a.notice_type=2 ");
			 Map<String, Object> params = new HashMap<String, Object>();
			 StdHealthbureauForm form = (StdHealthbureauForm) page.getForm();
		
			 if (StringUtils.isNotBlank(form.getNoticeId())){
				 sql.append(" and a.notice_id=:noticeId");
				 params.put("noticeId", form.getNoticeId());
			 }
			 if (StringUtils.isNotBlank(form.getNoticeNo())){
				 sql.append(" and a.notice_no=:noticeNo");
				 params.put("noticeNo", form.getNoticeNo());
			 }
			 if (StringUtils.isNotBlank(form.getNoticeName())){
				 sql.append(" and a.notice_name like :noticeName");
				 params.put("noticeName", "%" + form.getNoticeName().trim()+ "%");
			 }
			 if (StringUtils.isNotBlank(form.getRecpOrgId())){
				 sql.append(" and a.recp_org_id=:recpOrgId");
				 params.put("recpOrgId", form.getRecpOrgId());
			 }
			 if (StringUtils.isNotBlank(form.getScheduleBegTime())){
				 sql.append(" and a.schedule_beg_time=:scheduleBegTime");
				 params.put("scheduleBegTime", form.getScheduleBegTime());
			 }
			 if (StringUtils.isNotBlank(form.getScheduleEndTime())){
				 sql.append(" and a.schedule_end_time=:scheduleEndTime");
				 params.put("scheduleEndTime", form.getScheduleEndTime());
			 }
			 if (StringUtils.isNotBlank(form.getHeaBurName())){
				 sql.append(" and a.hea_bur_name=:heaBurName");
				 params.put("heaBurName", form.getHeaBurName());
			 }
			 return dao.getSqlPageMapList(sql.toString(), params, page);
		}
	/**
	  * @category 获取缴款通知书列表详情[卫生局]
	  * @Auther 周奇
	  * @date 2015年8月6日15:53:03
	  * @param page
	  * @return Pagination
	  */
	@Override
	public Pagination getPaynoticDetailForHeathBeau(Pagination page) {
		StringBuffer sql = new StringBuffer();
		/*-.append(" select a. trans_id as transId,  a.del_detail_id as delDetailId, ")
		.append(" a.goods_code as goodsCode, a.drug_id as drugId,a.pro_count as proCount,a.recp_count as recpCount, ")
		.append(" a.settle_sch_beg_time as settleSchBegTime,a.settle_sch_end_time as settleSchEndTime,a.is_in_settle_slip as isInSettleSlip, ")
		.append(" a.add_settle_slip_dttm as addSettleSlipDttm ,a.pro_price as proPrice, ")
		.append(" c.notice_id as noticeId,c.notice_no as noticeNo,c.notice_name as noticeName,")
		.append(" d.hosp_name as hospName ")
		.append(" from set_settletdetailinfo a ")
		.append(" left join set_paynotice_detail b on b.bank_acct_trans_info_id=a.trans_id")
		.append(" left join set_paynotice c on c.notice_id=b.payment_notice_id ")
		.append(" left join std_hospinfo d on d.hosp_id=a.hosp_id")
		.append(" left join std_healthbureau e on e.hea_bur_id=c.recp_org_id ")
		.append(" where c.notice_type=2 ");*/
		sql.append("SELECT d.trans_id AS transId, d.del_detail_id AS delDetailId, d.goods_code AS goodsCode, d.drug_id AS drugId, d.pro_count AS proCount, d.recp_count AS recpCount, d.settle_sch_beg_time AS settleSchBegTime, d.settle_sch_end_time AS settleSchEndTime, d.is_in_settle_slip AS isInSettleSlip, d.add_settle_slip_dttm AS addSettleSlipDttm, d.pro_price AS proPrice, d.pro_price * d.recp_count AS amount FROM set_paynotice a JOIN set_paynotice_detail b ON a.notice_id = b.bank_acct_trans_info_id JOIN set_paynotice_detail c ON a.notice_id = c.payment_notice_id JOIN set_settletdetailinfo d ON c.bank_acct_trans_info_id = d.trans_id WHERE b.payment_notice_id = :noticeId ");
		Map<String, Object> params = new HashMap<String, Object>();
		StdHealthbureauForm form = (StdHealthbureauForm) page.getForm();
		
		params.put("noticeId", form.getNoticeId());
		
		if (StringUtils.isNotBlank(form.getDelDetailId())){
			 sql.append(" and a.del_detail_id=:delDetailId");
			 params.put("delDetailId", form.getDelDetailId());
		 }
		if (StringUtils.isNotBlank(form.getGoodsCode())){
			 sql.append(" and a.goods_code=:goodsCode");
			 params.put("goodsCode", form.getGoodsCode());
		 }
		if (StringUtils.isNotBlank(form.getSettleSchBegTime())){
			 sql.append(" and a.settle_sch_beg_time=:settleSchBegTime");
			 params.put("settleSchBegTime", form.getSettleSchBegTime());
		 }
		if (StringUtils.isNotBlank(form.getSettleSchEndTime())){
			 sql.append(" and a.settle_sch_end_time=:settleSchEndTime");
			 params.put("settleSchEndTime", form.getSettleSchEndTime());
		 }
		
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}



	@Override
	public StdHealthbureau getStdHealthbureauById(String id) {
		return (StdHealthbureau) dao.getUniquerResult(StdHealthbureau.class,id);
	}



	@Override
	public Object getSetPaynoticeById(String id) {
		String sql="select * from set_paynotice where notice_id=:id ";
		Map<String,Object> param = new HashMap<String, Object>();
		param.put("id", id);
		return dao.getSqlMapUniqueResult(sql, param);
	}



	@Override
	public SetAcctInfo getSetAcctInfoById(String hospCode) {
		String sql = " from SetAcctInfo WHERE orgCode=:hospCode ";
		Query query = getSession().createQuery(sql);
		query.setParameter("hospCode", hospCode);
		return (SetAcctInfo) query.uniqueResult();
	}



	@Override
	public SetRelationship getSetRelationshipById(String id) {
		String sql = " from SetRelationship WHERE orgId=:id ";
		Query query = getSession().createQuery(sql);
		query.setParameter("id", id);
		return (SetRelationship) query.uniqueResult();
	}

}